"""
直接通过Supabase API创建数据库表
"""

import os
import sys
from supabase import create_client, Client

# 配置
SUPABASE_URL = 'https://wfmarihxfyyqljzaakbk.supabase.co'
SUPABASE_KEY = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6IndmbWFyaWh4Znl5cWxqemFha2JrIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTcxNTM5MDQsImV4cCI6MjA3MjcyOTkwNH0.9KoJUGhMbMNyNZhFHaqUIv3mt4z26XnbN4sMq0XlUxo'

# 使用service role key（权限更高）
SERVICE_KEY = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6IndmbWFyaWh4Znl5cWxqemFha2JrIiwicm9sZSI6InNlcnZpY2Vfcm9sZSIsImlhdCI6MTc1NzE1MzkwNCwiZXhwIjoyMDcyNzI5OTA0fQ.XJXgms9cd65EWsNXFeV7YDyvsGFaUStrUxcPQYrEpZQ'

def create_tables():
    """创建数据库表"""
    print("使用Service Role创建数据库表...")
    
    # 使用service role权限
    supabase: Client = create_client(SUPABASE_URL, SERVICE_KEY)
    
    # 核心建表SQL语句
    sql_statements = [
        # 创建用户表
        """
        CREATE TABLE IF NOT EXISTS users (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            email TEXT UNIQUE,
            username TEXT UNIQUE,
            display_name TEXT,
            avatar_url TEXT,
            created_at TIMESTAMPTZ DEFAULT NOW(),
            updated_at TIMESTAMPTZ DEFAULT NOW()
        );
        """,
        
        # 创建会话表  
        """
        CREATE TABLE IF NOT EXISTS sessions (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            user_id UUID DEFAULT '00000000-0000-0000-0000-000000000000'::uuid,
            title TEXT NOT NULL,
            role_id TEXT,
            model TEXT NOT NULL DEFAULT 'deepseek-v3-1-250821',
            search_enabled BOOLEAN DEFAULT false,
            memo TEXT,
            memo_enabled BOOLEAN DEFAULT false,
            is_favorite BOOLEAN DEFAULT false,
            is_archived BOOLEAN DEFAULT false,
            created_at TIMESTAMPTZ DEFAULT NOW(),
            updated_at TIMESTAMPTZ DEFAULT NOW()
        );
        """,
        
        # 创建消息表
        """
        CREATE TABLE IF NOT EXISTS messages (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
            role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
            content TEXT NOT NULL,
            model TEXT,
            created_at TIMESTAMPTZ DEFAULT NOW(),
            message_index INTEGER NOT NULL
        );
        """,
        
        # 创建自定义角色表
        """
        CREATE TABLE IF NOT EXISTS custom_roles (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            user_id UUID DEFAULT '00000000-0000-0000-0000-000000000000'::uuid,
            name TEXT NOT NULL,
            title TEXT,
            input_spec TEXT NOT NULL,
            output_spec TEXT NOT NULL,
            custom_traits JSONB DEFAULT '[]',
            system_prompt_snapshot TEXT NOT NULL,
            usage_count INTEGER DEFAULT 0,
            last_used_at TIMESTAMPTZ,
            created_at TIMESTAMPTZ DEFAULT NOW(),
            updated_at TIMESTAMPTZ DEFAULT NOW()
        );
        """,
        
        # 创建默认用户
        """
        INSERT INTO users (id, display_name, username)
        VALUES ('00000000-0000-0000-0000-000000000000', 'Default User', 'default')
        ON CONFLICT (id) DO UPDATE SET
            display_name = EXCLUDED.display_name,
            username = EXCLUDED.username;
        """
    ]
    
    try:
        for i, sql in enumerate(sql_statements, 1):
            print(f"执行SQL语句 {i}/{len(sql_statements)}...")
            
            try:
                # 使用SQL RPC调用
                result = supabase.rpc('exec_sql', {'sql': sql.strip()}).execute()
                print(f"[OK] 语句 {i} 执行成功")
                
            except Exception as e:
                print(f"[WARN] 语句 {i} 执行失败: {e}")
                # 继续执行下一条语句
                continue
                
        print("\\n[SUCCESS] 数据库表创建完成!")
        return True
        
    except Exception as e:
        print(f"[ERROR] 创建表失败: {e}")
        return False

def main():
    print("=== 数据库表创建工具 ===")
    
    if create_tables():
        print("\\n表创建成功！现在可以运行数据迁移了")
    else:
        print("\\n表创建失败，需要手动在Supabase中创建")
        print("请访问: https://wfmarihxfyyqljzaakbk.supabase.co")
        print("在SQL Editor中执行 database/init.sql")

if __name__ == "__main__":
    main()